package com.jsbj.Excel;

import com.jsbj.tools.ReadExcleDefine;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import java.util.regex.Pattern;

public class ReadXSSExcelUtil {

	private XSSFWorkbook wb = null;
	private XSSFSheet sheet = null;
	private XSSFRow row = null;
	private int sheetNum = 0;
	private int rowNum = 0;
	private FileInputStream fis = null;
	private File file = null;
	// 标头定义map
	private Map<Integer, ReadExcleDefine> maps;

	public ReadXSSExcelUtil(File file) {
		this.file=file;
	}
	

	public ReadXSSExcelUtil(Map<Integer, ReadExcleDefine> maps,
                            MultipartFile uploadfile) throws Exception {
		String filename = uploadfile.getOriginalFilename();
		String[] fileIngredent = filename.split("\\.");
		File f = File.createTempFile(fileIngredent[0], "."+fileIngredent[1]);
		// 将mutipartFile转换成file
		uploadfile.transferTo(f);
		// MultipartFile转成File
		//CommonsMultipartFile cf = (CommonsMultipartFile) uploadfile;
	    //DiskFileItem fi = (DiskFileItem) cf.getFileItem();
	    //	this.file = fi.getStoreLocation();
		this.file = f;
		open();
		this.sheetNum = 0;
		this.maps = maps;
		// 检查表头是否正确 
		checkHeads();

	}

	public ReadXSSExcelUtil(Map<Integer, ReadExcleDefine> maps,
                            MultipartFile uploadfile, Integer num) throws Exception {
		String filename = uploadfile.getOriginalFilename();
		String[] fileIngredent = filename.split("\\.");
		File f = File.createTempFile(fileIngredent[0], "."+fileIngredent[1]);
		// 将mutipartFile转换成file
		uploadfile.transferTo(f);
		// MultipartFile转成File
		//CommonsMultipartFile cf = (CommonsMultipartFile) uploadfile;
		//DiskFileItem fi = (DiskFileItem) cf.getFileItem();
		//	this.file = fi.getStoreLocation();
		this.file = f;
		open();
		this.sheetNum = 0;
		this.maps = maps;
		// 检查表头是否正确
		checkHeads(num);

	}

	//获取流文件
	private static void inputStreamToFile(InputStream ins, File file) {
		try {
			OutputStream os = new FileOutputStream(file);
			int bytesRead = 0;
			byte[] buffer = new byte[8192];
			while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
				os.write(buffer, 0, bytesRead);
			}
			os.close();
			ins.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	/**
	 * 读取excel文件获得XSSFWorkbook对象
	 */
	public void open() throws IOException {
		fis = new FileInputStream(file);
		wb = new XSSFWorkbook(fis);
		fis.close();
	}

	/**
	 * 返回sheet表数目
	 */
	public int getSheetCount() {
		int sheetCount = -1;
		sheetCount = wb.getNumberOfSheets();
		return sheetCount;
	}

	/**
	 * sheetNum下的记录行数
	 */
	public int getRowCount() throws Exception {
		if (wb == null)
			throw new Exception("WorkBook为空");
		XSSFSheet sheet = wb.getSheetAt(this.sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}

	/**
	 * 读取指定sheetNum的rowCount
	 */
	public int getRowCount(int sheetNum) {
		XSSFSheet sheet = wb.getSheetAt(sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}

	/**
	 * 得到指定行的内容
	 */
	public String[] readExcelLine(int lineNum) {
		return readExcelLine(this.sheetNum, lineNum);
	}

	/**
	 * 指定工作表和行数的内容
	 */
	public String[] readExcelLine(int sheetNum, int lineNum) {
		if (sheetNum < 0 || lineNum < 0)
			return null;
		String[] strExcelLine = null;
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(lineNum);

			int cellCount = row.getLastCellNum();
			strExcelLine = new String[cellCount + 1];
			for (int i = 0; i <= cellCount; i++) {
				strExcelLine[i] = readStringExcelCell(lineNum, i);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelLine;
	}

	/**
	 * 读取指定列的内容
	 */
	public String readStringExcelCell(int cellNum) {
		return readStringExcelCell(this.rowNum, cellNum);
	}

	/**
	 * 指定行和列编号的内容
	 */
	public String readStringExcelCell(int rowNum, int cellNum) {
		return readStringExcelCell(this.sheetNum, rowNum, cellNum);
	}

	/**
	 * 指定工作表、行、列下的内容
	 */
	public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0)
			return "";
		String strExcelCell = "";
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);

			Cell cell = row.getCell((short) cellNum);

			if (cell != null) {
				switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_NUMERIC:
				case XSSFCell.CELL_TYPE_FORMULA: {
					// 判断当前的cell是否为Date
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						// 如果是Date类型则，转化为Data格式
						Date date = cell.getDateCellValue();
						SimpleDateFormat sdf = new SimpleDateFormat(
								"yyyy-MM-dd");
						strExcelCell = sdf.format(date);
					}
					// 如果是纯数字
					else {
						// 取得当前Cell的数值
						//魏村进度管理发现小数因为.longValue()方法丢失精度，去掉
//						strExcelCell = String.valueOf(cell.getNumericCellValue());
						double strExcelCell1 = Double.valueOf(cell.getNumericCellValue());
						DecimalFormat decimalFormat = new DecimalFormat("###################.###########");
						strExcelCell = decimalFormat.format(strExcelCell1);
					}
					break;
				}
				case XSSFCell.CELL_TYPE_STRING:
					strExcelCell = cell.getStringCellValue();
					break;
				case XSSFCell.CELL_TYPE_BLANK:
					strExcelCell = "";
					break;
				default:
					strExcelCell = "";
					break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelCell;
	}

	public void checkHeads() throws Exception {
		String msg = "";
		String[] heads = null;
		try {
			if (getRowCount() == 0) {
				throw new Exception("没有数据!");
			} else {
				heads = readExcelLine(0);
			}
			String fieldname;// 需要检测的属性名称
			ReadExcleDefine re = null;
			for (int i = 0; i < heads.length - 1; i++) {
				fieldname = heads[i];
				re = maps.get(i + 1);
				if (null != re && !re.getName().equals(fieldname))
					msg += "列名:" + fieldname + "有误,<br/>";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (!"".equals(msg))
			throw new Exception(msg);
	}

	public void checkHeads(Integer num) throws Exception {
		String msg = "";
		String[] heads = null;
		try {
			if (getRowCount() == 0) {
				throw new Exception("没有数据!");
			} else {
				heads = readExcelLine(num);
			}
			String fieldname;// 需要检测的属性名称
			ReadExcleDefine re = null;
			for (int i = 0; i < heads.length - 1; i++) {
				fieldname = heads[i];
				re = maps.get(i + 1);
				if (!re.getName().equals(fieldname))
					msg += "列名:" + fieldname + "有误,<br/>";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (!"".equals(msg))
			throw new Exception(msg);
	}

	public String checkCellValue(int row, int column, String value) {
		Pattern p = Pattern.compile("[0-9]*");
		String msg = "";
		int cellRow = row + 1;
		int cellColumn = column + 1;
		// excle表头定义
		ReadExcleDefine re = maps.get(cellColumn);
		if (re == null)
			return "";
		// 单元格名称
		String cellName = "第" + cellRow + "行" + "第" + cellColumn + "列";
		// 检查值
		if ((value == null || "".equals(value)) && !re.isNullable())
			msg += cellName + "不能为空,<br/>";
		if (value != null && !"".equals(value)) {
			if (value.length() > re.getMaxLength())
				msg += cellName + "最多" + re.getMaxLength() + "位,<br/>";
			if (re.getType().equals("Integer") && !p.matcher(value).matches())
				msg += cellName + "必须为整数,<br/>";
			if (re.getType().equals("Date") && !isValidDate(value))
				msg += cellName + "必须为日期型,格式yyyy-MM-dd,<br/>";
			if (re.getType().equals("Integer") && re.getMaxValue() != null
					&& Integer.parseInt(value) > re.getMaxValue())
				msg += cellName + "超出范围，最大"+re.getMaxValue()+",<br/>";
		}
		return msg;
	}

	private boolean isValidDate(String str) {
		boolean convertSuccess = true;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		try {
			format.setLenient(false);
			format.parse(str);
		} catch (ParseException e) {
			convertSuccess = false;
		}
		return convertSuccess;
	}

	public void setRowNum(int rowNum) {
		this.rowNum = rowNum;
	}

	public void setSheetNum(int sheetNum) {
		this.sheetNum = sheetNum;
	}

	public void setFile(File file) {
		this.file = file;
	}
}
